﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class BondsmanDocInfoDAL : DBBase
    {
        public static DataTable LoadBondsmanDocAll(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [BONDSMAN_DOC_ID]
                                                      ,[BONDSMAN_DOC_NAME]
                                                      ,[BONDSMAN_DOC_FILE]
                                                      ,[BONDSMAN_DOC_EXTENSION]
                                                      ,[BONDSMAN_ID]
                                              FROM [CAR_LOAN].[dbo].[BONDSMAN_DOC] WHERE [BONDSMAN_ID] = @BONDSMAN_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@BONDSMAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static BondsmanDocItem LoadBondsmanDoc(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [BONDSMAN_DOC_ID]
                                                      ,[BONDSMAN_DOC_NAME]
                                                      ,[BONDSMAN_DOC_FILE]
                                                      ,[BONDSMAN_DOC_EXTENSION]
                                                      ,[BONDSMAN_ID]
                                              FROM [CAR_LOAN].[dbo].[BONDSMAN_DOC] WHERE [BONDSMAN_DOC_ID] = @BONDSMAN_DOC_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@BONDSMAN_DOC_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    BondsmanDocItem item = new BondsmanDocItem();
                    item.BONDSMAN_DOC_ID = Convert.ToInt32(dt.Rows[0]["BONDSMAN_DOC_ID"].ToString());
                    item.BONDSMAN_ID = Convert.ToInt32(dt.Rows[0]["BONDSMAN_ID"].ToString());
                    item.BONDSMAN_DOC_NAME = dt.Rows[0]["BONDSMAN_DOC_NAME"].ToString();
                    item.BONDSMAN_DOC_EXTENSION = dt.Rows[0]["BONDSMAN_DOC_EXTENSION"].ToString();
                    item.BONDSMAN_DOC_FILE = dt.Rows[0]["BONDSMAN_DOC_FILE"] as byte[];
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void Insert(BondsmanDocItem item, byte[] file)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[BONDSMAN_DOC]
                                                       ([BONDSMAN_DOC_NAME]
                                                       ,[BONDSMAN_DOC_FILE]
                                                       ,[BONDSMAN_DOC_EXTENSION]
                                                       ,[BONDSMAN_ID])
                                                 VALUES
                                                      ( @BONDSMAN_DOC_NAME
                                                       ,@BONDSMAN_DOC_FILE
                                                       ,@BONDSMAN_DOC_EXTENSION
                                                       ,@BONDSMAN_ID)", DBBase.con);

                command.Parameters.AddWithValue("@BONDSMAN_DOC_NAME", item.BONDSMAN_DOC_NAME);
                command.Parameters.AddWithValue("@BONDSMAN_DOC_FILE", file);
                command.Parameters.AddWithValue("@BONDSMAN_DOC_EXTENSION", item.BONDSMAN_DOC_EXTENSION);
                command.Parameters.AddWithValue("@BONDSMAN_ID", item.BONDSMAN_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void Delete(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"DELETE FROM [CAR_LOAN].[dbo].[BONDSMAN_DOC]
                        WHERE BONDSMAN_DOC_ID = @BONDSMAN_DOC_ID", DBBase.con);
                command.Parameters.AddWithValue("@BONDSMAN_DOC_ID", id);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void DeleteAll(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"DELETE FROM [CAR_LOAN].[dbo].[BONDSMAN_DOC]
                                                    WHERE BONDSMAN_ID = @BONDSMAN_ID", DBBase.con);
                command.Parameters.AddWithValue("@BONDSMAN_ID", id);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
